﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Linq;
using PropertyCreator.Models;

namespace PropertyCreator
{
    public class DbServer
    {
        private string address;
        private string user;
        private string password;
        private string dbname;
        private string connStr = "";

        public DbServer(string address, string user, string password, string dbname)
        {
            this.address = address;
            this.user = user;
            this.password = password;
            this.dbname = dbname;
            connStr = string.Format(@"Data Source={0};Initial Catalog={3};User ID={1};Password={2};",
                address, user, password, dbname);
        }

        public bool TestConnection()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                }
                return true;
            }
            catch (Exception) { return false; }
        }

        public IList<string> GetTableList()
        {

            try
            {
                SqlConnection conn = new SqlConnection(connStr);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Open();
                SqlCommand cmd = new SqlCommand("select name from sysobjects where xtype='u'", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                IList<string> list = new List<string>();
                while (reader.Read())
                {
                    list.Add(reader["name"].ToString());
                }
                conn.Close();
                return list;
            }
            catch (Exception) { return new List<string>(); }

        }

        public List<TableRow> GetTableRowList(string tableName)
        {
            try
            {
                List<TableRow> list = new List<TableRow>();
                string SqlStr = @"select syscolumns.name as ColName ,systypes.name as ColTypeName    
                                           ,syscolumns.length as Len, sys.extended_properties.value as Mark   
                                           ,syscolumns.isnullable as AllowNull    
                                           ,IsPK = Case    
                                           when exists   
                                           (   
                                           select 1    
                                           from sysobjects   
                                           inner join sysindexes    
                                           on sysindexes.name = sysobjects.name   
                                           inner join sysindexkeys   
                                           on sysindexes.id = sysindexkeys.id    
                                           and  sysindexes.indid = sysindexkeys.indid    
                                           where xtype='PK'    
                                           and parent_obj = syscolumns.id     
                                           and sysindexkeys.colid = syscolumns.colid    
                                           ) then 1   
                                           else 0   
                                           end   
                                           ,IsIdentity = Case syscolumns.status when 128 then 1 else 0 end    
                                           from syscolumns   
                                           inner join systypes on    
                                           (   
                                           syscolumns.xtype = systypes.xtype   
                                           and systypes.name <>'_default_'    
                                           and systypes.name<>'sysname'    
                                           )   
                                           left outer join sys.extended_properties on   
                                           (   
                                           sys.extended_properties.major_id=syscolumns.id    
                                           and minor_id=syscolumns.colid   
                                           )   
                                           where syscolumns.id = (select id from sysobjects where name='" + tableName + @"')   
                                           order by syscolumns.colid";
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(SqlStr, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        TableRow row = new TableRow();
                        row.ColName = reader["ColName"].ToString();
                        row.ColType = reader["ColTypeName"].ToString();
                        row.Comment = reader["Mark"].ToString();
                        row.ColLength = reader["Len"].ToString();
                        row.IsPk = Convert.ToBoolean(reader["IsPK"]);
                        row.IsNull = Convert.ToBoolean(reader["AllowNull"]);
                        row.IsIdentity = (reader["IsIdentity"].ToString() == "1") ? true : false;
                        list.Add(row);
                    }
                }
                return list;
            }
            catch (Exception) { return new List<TableRow>(); }
        }

        /// <summary>
        /// 获取数据字典
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<DataDict> GetDataDictList(string tableName)
        {
            string SqlStr = string.Format(@"select  t.table_name_c ,
        t.field_name ,
        t.field_name_c
from    data_dict t
where   t.table_name='{0}'", tableName);

            List<DataDict> list = new List<DataDict>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Open();
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DataDict data = new DataDict();
                    data.TableNameC = reader["table_name_c"].ToString();
                    data.FieldName = reader["field_name"].ToString();
                    data.FieldNameC = reader["field_name_c"].ToString();
                    list.Add(data);
                }
            }
            return list;
        }

        public string GetNetType(string dbType, bool nullable)
        {
            string netType = string.Empty;
            switch (dbType)
            {
                case "bigint":
                    netType = "long?";
                    break;
                case "binary":
                    netType = "byte[]";
                    break;
                case "bit":
                    netType = "bool?";
                    break;
                case "char":
                    netType = "string";
                    break;
                case "date":
                    netType = "DateTime?";
                    break;
                case "datetime":
                    netType = "DateTime?";
                    break;
                case "decimal":
                    netType = "decimal?";
                    break;
                case "float":
                    netType = "double?";
                    break;
                case "image":
                    netType = "byte[]";
                    break;
                case "int":
                    netType = "int?";
                    break;
                case "money":
                    netType = "decimal?";
                    break;
                case "nchar":
                    netType = "string";
                    break;
                case "ntext":
                    netType = "string";
                    break;
                case "numeric":
                    netType = "decimal?";
                    break;
                case "nvarchar":
                    netType = "string";
                    break;
                case "real":
                    netType = "float?";
                    break;
                case "smalldatetime":
                    netType = "DateTime?";
                    break;
                case "smallint":
                    netType = "short?";
                    break;
                case "smallmoney":
                    netType = "decimal?";
                    break;
                case "text":
                    netType = "string";
                    break;
                case "tinyint":
                    netType = "byte?";
                    break;
                case "uniqueidentifier":
                    netType = "Guid?";
                    break;
                case "varchar":
                    netType = "string";
                    break;
                case "datetimeoffset":
                    netType = "DateTimeOffset";
                    break;
                case "xml":
                    netType = "string";
                    break;
                case "varbinary":
                    netType = "byte[]";
                    break;
                case "datetime2":
                    netType = "DateTime";
                    break;
                case "timestamp":
                    netType = "byte[]";
                    break;
                case "time":
                    netType = "TimeSpan";
                    break;
                case "sysname":
                    netType = "string";
                    break;
                default:
                    throw new NotSupportedException("不支持的数据类型:" + dbType);
            }
            return nullable ? netType : netType.Replace("?", "");
        }
    }
}